How to retrieve data from Amazon Redshift in TIBCO Statistica using Amazon ODBC driver?

book

Article ID: KB0075077

calendar_today

Updated On:

Products	Versions
Spotfire Statistica	13.1 through 13.5

Description

User needs to pull in data from Amazon Redshift database for analysis.

Prerequisites:
1. In addition to the Statistica (Data Science - Workbench),  the Amazon Redshift ODBC driver must be downloaded and installed. Download location:   https://docs.aws.amazon.com/redshift/latest/mgmt/configure-odbc-connection.html#install-odbc-driver-windows.
2. When using Statistica Enterprise Manager, the user should have permissions to create a database connection (Either System Admin (SADM) or Database Admin (EXTDB_ADM) and Data Admin (DADM))
3. Details of the Amazon Redshift server and user credentials.

Issue/Introduction

This article provides steps that will resolve errors when trying to pull back data from Amazon Redshift database to Statistica.

Environment

Statistica is built for Windows operating systems only.

Resolution

1. Launch Control Panel--> System and Security--> Administrative Tools --> ODBC Data Sources --> System DSN tab and click on the Add button
2. Select "Amazon Redshift (x64)" driver:
Amazon Redshift (x86) data source or driver

3.  Name the data source appropriately and enter the name/IP address of the Amazon Redshift server along with username and password details. Click on Test ... button to see if the connection succeeds. If the connection fails, contact your database administrator. Enter details of your default database:

DSN setup

Connection Test successful

5. Within Statistica (or Enterprise Manager), create a new ADO.NET Database Connection. From Statistica desktop, the user may choose File-->External Data-->Query-->Create.  Inside Enterprise Manager, right-click on the Data connections and click on New ADO.NET Database Connection:

6. Change the data source:

Change data source

7. Select "Microsoft ODBC Data Source (ODBC) as the Data source and "Amazon Redshift ODBC DSN" as data source specification:

Change to Microsoft ODBC Data Source and Amazon Redshit ODBC DSN

8. Click the "Test Connection" button and if the test is successful, click "OK". Then name the database connection.

The Amazon Redshift database can now be used to create data configuration. See steps 8 through 12 of this knowledge base article:  https://support.tibco.com/s/article/How-to-pull-data-from-Teradata-into-Statistica-via-ODBC

Note: The ODBC connector does not support generic SQL filtering. If SQL filtering is necessary, please use the PostgreSQL driver (https://support.tibco.com/s/article/How-to-query-data-from-Amazon-Redshift-into-Statistica-Statistica-Enterprise-for-analyses-using-PostGres-ODBC-Driver)